package com.pub.dbtools;

import org.jeecgframework.core.util.PropertiesUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;

public class DBQuerySql {
/*	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	public static  <T> List<T> findObjForJdbc(String sql, int page, int rows,
									  Class<T> clazz) {
		List<T> rsList = new ArrayList<T>();
		// 封装分页SQL
		sql = JdbcTools.CreatePageSql(sql, page, rows);

		List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);

		T po = null;
		for (Map<String, Object> m : mapList) {
			try {
				po = clazz.newInstance();
				ObjectUtil.copyMap2Bean_Nobig(po, m);
				rsList.add(po);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rsList;
	}*/


	private static final ArrayList makeQuery(String dbname, String strSql,
			Connection con, int innum) {

		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		ArrayList vectStore = new ArrayList();
		String tableName = "";
		try {
			stmtDB = con.createStatement();

			String strTemp = strSql.toLowerCase().trim();
			rsetTemp = stmtDB.executeQuery(strSql.trim());

			vectStore = makeValue(dbname, rsetTemp, innum);

			if (rsetTemp != null)
				rsetTemp.close();
			stmtDB.close();
			stmtDB = null;
			rsetTemp = null;
			return vectStore;
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			return vectStore;
		} finally {
			stmtDB = null;
			rsetTemp = null;
		}
	}

	private static final ArrayList makeQuery(String strSql, Connection con,
			int npageno, int npagenum) {
		if (strSql.equals("")) {
			return new ArrayList();
		}
		PreparedStatement callsta;
		ResultSet rsetTemp;
		int nCode;
		ArrayList vectStore = new ArrayList();
		String tableName = "";
		try {

			String st = "{call nPageProc(?,?,?)}";
			callsta = con.prepareStatement(st);

			callsta.setString(1, strSql);
			callsta.setInt(2, npageno);
			callsta.setInt(3, npagenum);

			callsta.execute();
			callsta.getMoreResults();

			rsetTemp = callsta.getResultSet();
			int totrec = 0;
			while (rsetTemp.next()) {
				totrec = rsetTemp.getInt(2);
			}

			callsta.getMoreResults();
			rsetTemp = callsta.getResultSet();
			int nrows3 = rsetTemp.getRow();

			vectStore = makeValue(rsetTemp, 20000);

			if (rsetTemp != null)
				rsetTemp.close();
			callsta.close();
			callsta = null;
			rsetTemp = null;
			vectStore.add(String.valueOf(totrec));
			return vectStore;
		} catch (SQLException e) {
			e.printStackTrace();
			callsta = null;
			rsetTemp = null;
			return vectStore;
		} finally {
			callsta = null;
			rsetTemp = null;
		}
	}

	private static final ArrayList makeQuery(String strSql, Connection con,
			int innum) {
		if (strSql.equals("")) {
			return new ArrayList();
		}
		Statement stmtDB = null;
		ResultSet rsetTemp = null;
		int nCode;
		ArrayList vectStore = new ArrayList();
		String tableName = "";
		try {
			stmtDB = con.createStatement();

			String strTemp = strSql.toLowerCase().trim();

			rsetTemp = stmtDB.executeQuery(strSql.trim());

			vectStore = makeValue(rsetTemp, innum);

			if (stmtDB != null)
				stmtDB.close();
			if (rsetTemp != null)
				rsetTemp.close();

			stmtDB = null;
			rsetTemp = null;
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
		} finally {
			try {
				if (stmtDB != null)
					stmtDB.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmtDB = null;
			rsetTemp = null;
		}
		return vectStore;
	}

	private static final ArrayList makeQuery(HashMap tmpmap, Connection con,
			int innum) {

		String procedure_name = "prcPageResult";
		String call_procedure_string = "{call " + procedure_name
				+ "(?,?,?,?,?,?,?,?)}";

		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		ArrayList vectStore = new ArrayList();
		String tableName = "";
		try {

			String selesql = ((String) tmpmap.get("fd_SearchSql".toLowerCase()))
					.toLowerCase();
			selesql = selesql.trim();
			if (selesql.startsWith("select ")) {
				selesql = selesql.substring(selesql.indexOf("select ") + 7);
			}
			String fromsql = ((String) tmpmap.get("fd_FromSql".toLowerCase()))
					.toLowerCase();
			fromsql = fromsql.trim();
			if (fromsql.startsWith("from ")) {
				fromsql = fromsql.substring(fromsql.indexOf("from ") + 5);
			}
			String wheresql = ((String) tmpmap.get("fd_WhereSql".toLowerCase()))
					.toLowerCase();
			wheresql = wheresql.trim();
			if (wheresql.startsWith("where ")) {
				wheresql = wheresql.substring(wheresql.indexOf("where ") + 6);
			}
			String ordersql = ((String) tmpmap.get("fd_OrderSql".toLowerCase()))
					.toLowerCase();
			ordersql = ordersql.trim();
			if (ordersql.indexOf(" by ") > 0) {
				ordersql = ordersql.substring(ordersql.indexOf(" by ") + 4);
			}
			if (ordersql.equals("")) {
				ordersql = "ID_NUM";
			}
			int desctype = 0;
			if (ordersql.trim().endsWith("desc")) {
				desctype = 1;
			}
			String pkname = ((String) tmpmap.get("fd_PkName".toLowerCase()))
					.toLowerCase();

			String spageno = (String) tmpmap.get("fd_pageno");
			int npageno = Integer.parseInt(spageno);

			String spagenum = (String) tmpmap.get("fd_pagenum");
			int npagenum = Integer.parseInt(spagenum);

			CallableStatement callable_statement = con
					.prepareCall(call_procedure_string);

			callable_statement.setInt(1, npageno);
			callable_statement.setString(2, selesql);
			callable_statement.setString(3, fromsql);
			callable_statement.setString(4, wheresql);
			callable_statement.setString(5, ordersql);
			callable_statement.setInt(6, desctype);
			callable_statement.setString(7, pkname);
			callable_statement.setInt(8, npagenum);

			rsetTemp = callable_statement.executeQuery();

			vectStore = makeValue(rsetTemp, innum);

			if (rsetTemp != null)
				rsetTemp.close();

			callable_statement.close();
			rsetTemp = null;
			return vectStore;
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			return vectStore;
		} finally {
			stmtDB = null;
			rsetTemp = null;
		}
	}

	private static final ArrayList makeValue(ResultSet rsetTemp, int innum) {
		Statement stmtDB;
		String strValue = "";
		String strColName = "";
		int nRows = 0;
		int nCols;
		int nType;
		ArrayList vectStore = new ArrayList();
		ResultSetMetaData metaTemp;

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		String nowdb = tmpvo.readProperty("jdbc.dbType");

		try {
			metaTemp = rsetTemp.getMetaData();
			nCols = metaTemp.getColumnCount();
			String[] tmpstr = new String[nCols];
			if (nowdb.toLowerCase().startsWith("mysql")) {
				for (int i = 0; i < nCols; i++) {
					tmpstr[i] = metaTemp.getColumnLabel(i + 1);
				}
			} else {
				for (int i = 0; i < nCols; i++) {
					tmpstr[i] = metaTemp.getColumnName(i + 1);
				}
			}
			vectStore.add(tmpstr);

			String strTypeName;
			int totrow = 0;

			while (rsetTemp.next()) {
				if (innum > 0 && totrow >= innum) {
					break;
				}
				tmpstr = new String[nCols];
				for (int i = 0; i < nCols; i++) {
					nType = metaTemp.getColumnType(i + 1);
					strTypeName = metaTemp.getColumnTypeName(i + 1);

					tmpstr[i] = "";

					try {
						switch (nType) {
						case -1:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								if (nowdb.toLowerCase().startsWith("sqlserver")) {
								} else {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnName(i + 1));
								}
							}
							break;
						case -4:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getBlob(metaTemp
										.getColumnLabel(i + 1)) == null) {
									tmpstr[i] = "";
								} else {
									tmpstr[i] = rsetTemp
											.getBlob(
													metaTemp.getColumnLabel(i + 1))
											.toString().trim();
								}
							} else {
								tmpstr[i] = "";
							}
							break;
						case -5:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = String.valueOf(rsetTemp
										.getString(metaTemp
												.getColumnLabel(i + 1)));
							} else {
								tmpstr[i] = String.valueOf(rsetTemp
										.getString(metaTemp
												.getColumnName(i + 1)));
							}
							break;
						case -6:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = String
										.valueOf(rsetTemp.getInt(metaTemp
												.getColumnLabel(i + 1)));
							} else {
								tmpstr[i] = String.valueOf(rsetTemp
										.getInt(metaTemp.getColumnName(i + 1)));

							}
							break;
						case 1:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								tmpstr[i] = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));

							}
							break;
						case 12:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null) {
									tmpstr[i] = "";
								} else {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1));
								}
							} else {
								Object tmpResult = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));
								if (tmpResult == null) {
									tmpstr[i] = "";
								} else {
									tmpstr[i] = (String) tmpResult;
								}
							}
							break;
						case 2:
							String values = "";
							if (nowdb.toLowerCase().startsWith("mysql")) {
								values = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								values = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));

							}
							if (values == null) {
								values = "";
							} else {
								if (values.toUpperCase().equals("NULL"))
									values = "";
							}
							tmpstr[i] = values;
							if (tmpstr[i].startsWith("."))
								tmpstr[i] = "0" + tmpstr[i];
							break;
						case 3:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnLabel(i + 1))
											.trim();
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();

							}
							break;
						case 4:
							String value = "";
							if (nowdb.toLowerCase().startsWith("mysql")) {
								value = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								value = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));

							}
							if (value == null) {
								value = "";
							} else {
								if (value.toUpperCase().equals("NULL"))
									value = "";
							}
							tmpstr[i] = value;
							break;
						case 5:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = String
										.valueOf(rsetTemp.getInt(metaTemp
												.getColumnLabel(i + 1)));
							} else {
								tmpstr[i] = String.valueOf(rsetTemp
										.getInt(metaTemp.getColumnName(i + 1)));

							}
							break;
						case 7:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnLabel(i + 1))
											.trim();
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();

							}
							break;
						case 8:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnLabel(i + 1))
											.trim();
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();

							}
							break;
						case 91:
							String temp = "";
							if (nowdb.toLowerCase().startsWith("mysql")) {

								temp = metaTemp.getColumnLabel(i + 1);

								if (rsetTemp.getString(temp) != null) {
									tmpstr[i] = rsetTemp.getDate(
											metaTemp.getColumnLabel(i + 1))
											.toString();
								}
							} else {
								temp = metaTemp.getColumnName(i + 1);

								if (rsetTemp.getString(temp) != null) {
									tmpstr[i] = rsetTemp.getDate(
											metaTemp.getColumnName(i + 1))
											.toString();
								}
							}
							break;
						case 92:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getTime(metaTemp
										.getColumnLabel(i + 1)) != null) {
									tmpstr[i] = rsetTemp.getTime(
											metaTemp.getColumnLabel(i + 1))
											.toString();
								}
							} else {
								if (rsetTemp.getTime(metaTemp
										.getColumnName(i + 1)) != null) {
									tmpstr[i] = rsetTemp.getTime(
											metaTemp.getColumnName(i + 1))
											.toString();
								}

							}
							break;
						case 93:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getTimestamp(metaTemp
										.getColumnLabel(i + 1)) != null) {

									tmpstr[i] = rsetTemp
											.getTimestamp(
													metaTemp.getColumnLabel(i + 1))
											.toString().trim();
								}
								if (tmpstr[i].length() > 19) {
									tmpstr[i] = tmpstr[i].substring(0, 19);
								}
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) != null) {
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();
								}
							}

							break;
						case 2004:
							tmpstr[i] = "";
							break;
						case 2005:
							// clob
							tmpstr[i] = "";
							break;
						default:
							tmpstr[i] = "字段类型不可识别";
						}
					} catch (Exception e) {
						e.printStackTrace();
						return vectStore;
					}
					if (tmpstr[i] == null)
						tmpstr[i] = "";
					tmpstr[i] = tmpstr[i].trim();

				}
				vectStore.add(tmpstr);
				totrow++;
			}
			metaTemp = null;
			return vectStore;
		} catch (SQLException e) {
			e.printStackTrace();
			metaTemp = null;
			return new ArrayList();
		} finally {
			metaTemp = null;
		}
	}

	private static final ArrayList makeValue(String nowdb, ResultSet rsetTemp,
			int innum) {
		Statement stmtDB;
		String strValue = "";
		String strColName = "";
		int nRows = 0;
		int nCols;
		int nType;
		ArrayList vectStore = new ArrayList();
		ResultSetMetaData metaTemp;


		try {
			metaTemp = rsetTemp.getMetaData();
			nCols = metaTemp.getColumnCount();
			String[] tmpstr = new String[nCols];
			if (nowdb.toLowerCase().startsWith("mysql")) {
				for (int i = 0; i < nCols; i++) {
					tmpstr[i] = metaTemp.getColumnLabel(i + 1);
				}
			} else {
				for (int i = 0; i < nCols; i++) {
					tmpstr[i] = metaTemp.getColumnName(i + 1);
				}
			}
			vectStore.add(tmpstr);

			String strTypeName;
			int totrow = 0;

			while (rsetTemp.next()) {
				if (innum > 0 && totrow >= innum) {
					break;
				}
				tmpstr = new String[nCols];
				for (int i = 0; i < nCols; i++) {
					nType = metaTemp.getColumnType(i + 1);
					strTypeName = metaTemp.getColumnTypeName(i + 1);

					tmpstr[i] = "";

					try {

						switch (nType) {
						case -1:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								tmpstr[i] = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));
							}
							break;
						case -4:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getBlob(metaTemp
										.getColumnLabel(i + 1)) == null) {
									tmpstr[i] = "";
								} else {
									tmpstr[i] = rsetTemp
											.getBlob(
													metaTemp.getColumnLabel(i + 1))
											.toString().trim();
								}
							} else {
								if (rsetTemp.getBlob(metaTemp
										.getColumnName(i + 1)) == null) {
									tmpstr[i] = "";
								} else {
									tmpstr[i] = rsetTemp
											.getBlob(
													metaTemp.getColumnName(i + 1))
											.toString().trim();
								}
							}
							break;
						case -5:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = String.valueOf(rsetTemp
										.getString(metaTemp
												.getColumnLabel(i + 1)));
							} else {
								tmpstr[i] = String.valueOf(rsetTemp
										.getString(metaTemp
												.getColumnName(i + 1)));
							}
							break;
						case -6:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = String
										.valueOf(rsetTemp.getInt(metaTemp
												.getColumnLabel(i + 1)));
							} else {
								tmpstr[i] = String.valueOf(rsetTemp
										.getInt(metaTemp.getColumnName(i + 1)));

							}
							break;
						case 1:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								tmpstr[i] = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));

							}
							break;
						case 12:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null) {
									tmpstr[i] = "";
								} else {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1));
								}
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) == null) {
									tmpstr[i] = "";
								} else {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnName(i + 1));
								}
							}
							break;
						case 2:
							String values = "";
							if (nowdb.toLowerCase().startsWith("mysql")) {
								values = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								values = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));

							}
							if (values == null) {
								values = "";
							} else {
								if (values.toUpperCase().equals("NULL"))
									values = "";
							}
							tmpstr[i] = values;
							if (tmpstr[i].startsWith("."))
								tmpstr[i] = "0" + tmpstr[i];
							break;
						case 3:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnLabel(i + 1))
											.trim();
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();

							}
							break;
						case 4:
							String value = "";
							if (nowdb.toLowerCase().startsWith("mysql")) {
								value = rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1));
							} else {
								value = rsetTemp.getString(metaTemp
										.getColumnName(i + 1));

							}
							if (value == null) {
								value = "";
							} else {
								if (value.toUpperCase().equals("NULL"))
									value = "";
							}
							tmpstr[i] = value;
							break;
						case 5:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								tmpstr[i] = String
										.valueOf(rsetTemp.getInt(metaTemp
												.getColumnLabel(i + 1)));
							} else {
								tmpstr[i] = String.valueOf(rsetTemp
										.getInt(metaTemp.getColumnName(i + 1)));

							}
							break;
						case 7:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnLabel(i + 1))
											.trim();
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();

							}
							break;
						case 8:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getString(metaTemp
										.getColumnLabel(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnLabel(i + 1))
											.trim();
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) == null)
									tmpstr[i] = "";
								else
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();

							}
							break;
						case 91:
							String temp = "";
							if (nowdb.toLowerCase().startsWith("mysql")) {

								temp = metaTemp.getColumnLabel(i + 1);

								if (rsetTemp.getString(temp) != null) {
									tmpstr[i] = rsetTemp.getDate(
											metaTemp.getColumnLabel(i + 1))
											.toString();
								}
							} else {
								temp = metaTemp.getColumnName(i + 1);

								if (rsetTemp.getString(temp) != null) {
									tmpstr[i] = rsetTemp.getDate(
											metaTemp.getColumnName(i + 1))
											.toString();
								}
							}
							break;
						case 92:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getTime(metaTemp
										.getColumnLabel(i + 1)) != null) {
									tmpstr[i] = rsetTemp.getTime(
											metaTemp.getColumnLabel(i + 1))
											.toString();
								}
							} else {
								if (rsetTemp.getTime(metaTemp
										.getColumnName(i + 1)) != null) {
									tmpstr[i] = rsetTemp.getTime(
											metaTemp.getColumnName(i + 1))
											.toString();
								}

							}
							break;
						case 93:
							if (nowdb.toLowerCase().startsWith("mysql")) {
								if (rsetTemp.getTimestamp(metaTemp
										.getColumnLabel(i + 1)) != null) {

									tmpstr[i] = rsetTemp
											.getTimestamp(
													metaTemp.getColumnLabel(i + 1))
											.toString().trim();
									if (tmpstr[i].length() > 19) {
										tmpstr[i] = tmpstr[i].substring(0, 19);
									}
								}
							} else {
								if (rsetTemp.getString(metaTemp
										.getColumnName(i + 1)) != null) {
									tmpstr[i] = rsetTemp.getString(
											metaTemp.getColumnName(i + 1))
											.trim();
								}
							}
							break;
						case 2004:
							tmpstr[i] = "";
							break;
						case 2005:
							tmpstr[i] = "";
							break;
						default:
							tmpstr[i] = "字段类型不可识别";
						}
					} catch (Exception e) {
						e.printStackTrace();
						return vectStore;
					}
					if (tmpstr[i] == null)
						tmpstr[i] = "";
					tmpstr[i] = tmpstr[i].trim();
				}
				vectStore.add(tmpstr);
				totrow++;
			}
			metaTemp = null;
			return vectStore;
		} catch (SQLException e) {
			e.printStackTrace();
			metaTemp = null;
			return new ArrayList();
		} finally {
			metaTemp = null;
		}
	}

	public static final ArrayList makeQuery(String str) {
		return makeQuery(str, 50000);
	}

	public static final ArrayList makeQuery(String str, int npageno,
			int npagenum) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String debugsign = tmpvo.readProperty("debugsign");

		ArrayList vectResult = null;
		Connection con = null;

		try {
			con = DBOperator.getConnect("sqlserver");

			if (con == null)
				return null;

			if (debugsign.equals("yes")) {
				System.err.println(str);
			}
			vectResult = makeQuery(str, con, npageno, npagenum);

			con.close();

			if (vectResult.size() > 0) {
				return vectResult;
			} else {
				return null;
			}
		} catch (Exception s) {
			try {
				System.err.println(s.toString());
				con.close();
			} catch (Exception s2) {
				s2.printStackTrace();
				return null;
			}
			s.printStackTrace();
			vectResult = null;
			return null;
		} finally {
		}
	}

	public static final ArrayList makeQuery(HashMap hashmap, int innum) {

		ArrayList vectResult = null;
		Connection con = null;

		try {
			con = DBOperator.getConnect("sqlserver");

			if (con == null)
				return null;

			if (innum == 0)
				innum = 20000;

			vectResult = makeQuery(hashmap, con, innum);

			con.close();

			if (vectResult.size() > 0) {
				return vectResult;
			} else {
				return null;
			}
		} catch (Exception s) {
			try {
				System.err.println(s.toString());
				con.close();
			} catch (Exception s2) {
				s2.printStackTrace();
				return null;
			}
			s.printStackTrace();
			vectResult = null;
			return null;
		} finally {
		}
	}

	public static final ArrayList makeQuery(String str, int innum) {

		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String debugsign = tmpvo.readProperty("debugsign");

		String dbname = "sqlserver";
		ArrayList vectResult = null;
		Connection con = null;

		String url = tmpvo.readProperty( "jdbc.url");
		String user = tmpvo.readProperty( "jdbc.username");
		String password = tmpvo.readProperty( "jdbc.password");

		try {
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			Properties info = new Properties();
			info.setProperty("proxool.maximum-connection-count", "100");
			info.setProperty("proxool.house-keeping-test-sql",
					"select getdate()");
			info.setProperty("user", user);
			info.setProperty("password", password);
			con = DriverManager.getConnection(
					"proxool.example:com.microsoft.jdbc.sqlserver.SQLServerDriver:"
							+ url, info);
			if (con == null)
				return null;

			if (innum == 0)
				innum = 20000;

			if (debugsign.equals("yes")) {
				System.err.println(str);
			}

			vectResult = makeQuery(str, con, innum);

			con.close();
			if (vectResult.size() > 0) {
				return vectResult;
			} else {
				return null;
			}
		} catch (Exception s) {
			try {
				System.err.println(s.toString());
				if (con != null) {
					con.close();
				}
			} catch (Exception s2) {
				s2.printStackTrace();
				return null;
			}
			s.printStackTrace();
			vectResult = null;
			return null;
		} finally {
		}
	}

	public static final ArrayList getSqlInfo(String strSql) {

		return makeMeta(strSql);
	}

	private static final ArrayList makeMeta(String strSql) {


		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");

		Connection con = null;

		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		ArrayList vectStore = new ArrayList();
		try {
			String currdb = tmpvo.readProperty("jdbc.dbType");
			con = DBOperator.getConnect(currdb);

			if (con == null)
				return null;

			stmtDB = con.createStatement();
			String strTemp = strSql.toLowerCase();

			rsetTemp = stmtDB.executeQuery(strSql);
			vectStore = makeDetaValue(strSql, rsetTemp, currdb);

			rsetTemp.close();
			stmtDB.close();
			con.close();
			return vectStore;

		} catch (SQLException e) {
			try {
				con.close();
			} catch (Exception s2) {
				s2.printStackTrace();
				return null;
			}
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			return new ArrayList();
		} finally {

		}
	}

	private static final ArrayList makeDetaValue(String inssql,
			ResultSet rsetTemp, String dbname) {
		String strValue = "";
		String strColName = "";
		int nRows = 0;
		int nCols;
		int nType;
		ArrayList vectStore = new ArrayList();
		ResultSetMetaData metaTemp;
		try {
			metaTemp = rsetTemp.getMetaData();
			nCols = metaTemp.getColumnCount();
			for (int i = 0; i < nCols; i++) {
				String[] tmpstr = new String[8];
				tmpstr[0] = metaTemp.getColumnName(i + 1);
				nType = metaTemp.getColumnType(i + 1);
				try {
					switch (nType) {
					case -1:
						tmpstr[2] = "string";
						break;
					case -4:
						break;
					case -5:
						tmpstr[2] = "number";
						break;
					case -6:
						tmpstr[2] = "number";
						break;
					case 1:
						tmpstr[2] = "string";
						break;
					case 12:
						tmpstr[2] = "string";
						break;
					case 2:
						tmpstr[2] = "number";
						break;
					case 3:
						tmpstr[2] = "number";
						break;
					case 4:
						tmpstr[2] = "number";
						break;
					case 5:
						tmpstr[2] = "number";
						break;
					case 7:
						tmpstr[2] = "string";
						break;
					case 8:
						tmpstr[2] = "string";
						break;
					case 91:
						tmpstr[2] = "date";
						break;
					case 92:
						tmpstr[2] = "time";
						break;
					case 93:
						tmpstr[2] = "datetime";
						break;
					case 2005:
						tmpstr[2] = "string";
						break;
					default:
						tmpstr[2] = "error";
					}
				} catch (Exception e) {
					e.printStackTrace();
					return vectStore;
				}
				tmpstr[7] = String
						.valueOf(metaTemp.getColumnDisplaySize(i + 1));

				if (tmpstr[2].equals("number")) {
					tmpstr[1] = String.valueOf(metaTemp.getPrecision(i + 1));
					tmpstr[7] = tmpstr[1];
				} else {
					tmpstr[1] = String.valueOf(metaTemp
							.getColumnDisplaySize(i + 1));
				}
				tmpstr[3] = metaTemp.getColumnLabel(i + 1).trim();
				tmpstr[4] = String.valueOf(metaTemp.isNullable(i + 1));
				tmpstr[5] = String.valueOf(metaTemp.getScale(i + 1));

				tmpstr[6] = getTableName(inssql, tmpstr[3]);
				if (tmpstr[2].toLowerCase().equals("date")) {
					tmpstr[7] = "10";
				}
				if (tmpstr[2].toLowerCase().equals("datetime")) {
					tmpstr[7] = "20";
				}
				vectStore.add(tmpstr);
			}
			metaTemp = null;
			return vectStore;
		} catch (Exception e) {
			e.printStackTrace();
			metaTemp = null;
			return vectStore;
		} finally {
			metaTemp = null;
		}
	}

	private static String getTableName(String inssql, String fieldname) {
		String inssqlnew = inssql.toUpperCase();
		inssqlnew = inssqlnew.trim().substring(7);

		if (inssqlnew.indexOf("FROM") > 0) {
			inssqlnew = inssqlnew.substring(0, inssqlnew.indexOf("FROM"));
		}
		inssqlnew = inssqlnew + ",";
		String[] tmparr = inssqlnew.split(",");
		for (int s = 0; s < tmparr.length; s++) {
			String nowstr = tmparr[s].toString().trim();
			if (nowstr.indexOf(fieldname.toUpperCase()) > 0) {
				if (nowstr.startsWith(",")) {
					nowstr = nowstr.substring(1);
				}
			}
			if (nowstr.indexOf(".") > 0) {
				nowstr = nowstr.substring(0, nowstr.indexOf("."));
				return nowstr;
			} else {
				return "a";
			}
		}
		return "a";
	}


}
